"""
cron: */30 * * * *
new Env('体育赛事数据采集');
"""
import requests
import os
import mysql.connector
from datetime import datetime

# 获取环境变量
host = os.environ.get('HOST')
user = os.environ.get('USER')
password = os.environ.get('PASSWORD')
database = os.environ.get('DATABASE')

print("正在获取环境变量...")
print(f"数据库地址：{host}")
print(f"数据库用户名：{user}")
print(f"数据库密码：{password}")
print(f"数据库名称：{database}")

# 发送HTTP请求获取比赛数据
print("发送HTTP请求获取体育赛事数据...")
headers = {
    'User-Agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/91.0.4472.124 Safari/537.36'
}

# 连接数据库
connection = mysql.connector.connect(
    host=host,
    user=user,
    password=password,
    database=database
)

cursor = connection.cursor()

# 创建表（如果不存在）
create_table_query = """
CREATE TABLE IF NOT EXISTS ningm_sports_match (
    id INT AUTO_INCREMENT PRIMARY KEY,
    match_id VARCHAR(50) NOT NULL,
    league_name VARCHAR(100),
    home_team VARCHAR(100),
    away_team VARCHAR(100),
    match_time DATETIME,
    match_status VARCHAR(20),
    home_score VARCHAR(10),
    away_score VARCHAR(10),
    sport_type VARCHAR(20),
    create_time DATETIME DEFAULT CURRENT_TIMESTAMP,
    INDEX idx_match_id (match_id),
    INDEX idx_sport_type (sport_type),
    INDEX idx_match_time (match_time),
    INDEX idx_create_time (create_time)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
"""
cursor.execute(create_table_query)

# 删除旧数据（保留3天内的数据）
print("删除旧数据...")
delete_query = "DELETE FROM ningm_sports_match WHERE create_time < DATE_SUB(NOW(), INTERVAL 3 DAY)"
cursor.execute(delete_query)

# 获取足球比赛数据
def get_football_matches():
    try:
        # 获取足球比赛数据的API
        url = 'https://api.dongqiudi.com/data/v1/recent_matches'
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            data = response.json()
            matches = data.get('matches', [])
            
            for match in matches:
                match_id = match.get('id', '')
                league_name = match.get('competition', {}).get('name', '')
                home_team = match.get('home', {}).get('name', '')
                away_team = match.get('away', {}).get('name', '')
                match_time = datetime.fromtimestamp(match.get('start_time', 0))
                match_status = match.get('status', '')
                home_score = str(match.get('home_score', ''))
                away_score = str(match.get('away_score', ''))
                
                print(f"正在保存足球比赛数据: {home_team} VS {away_team}")
                insert_query = """
                    INSERT INTO ningm_sports_match 
                    (match_id, league_name, home_team, away_team, match_time, match_status, 
                     home_score, away_score, sport_type) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    match_id, league_name, home_team, away_team, match_time, match_status,
                    home_score, away_score, 'football'
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取足球比赛数据失败: {str(e)}")

# 获取篮球比赛数据
def get_basketball_matches():
    try:
        # 获取NBA比赛数据的API
        url = 'https://api.sports.qq.com/match/nba/todayMatches'
        response = requests.get(url, headers=headers)
        
        if response.status_code == 200:
            data = response.json()
            matches = data.get('matches', [])
            
            for match in matches:
                match_id = match.get('matchId', '')
                league_name = 'NBA'
                home_team = match.get('homeTeam', {}).get('name', '')
                away_team = match.get('awayTeam', {}).get('name', '')
                match_time = datetime.fromtimestamp(match.get('startTime', 0))
                match_status = match.get('status', '')
                home_score = str(match.get('homeScore', ''))
                away_score = str(match.get('awayScore', ''))
                
                print(f"正在保存篮球比赛数据: {home_team} VS {away_team}")
                insert_query = """
                    INSERT INTO ningm_sports_match 
                    (match_id, league_name, home_team, away_team, match_time, match_status, 
                     home_score, away_score, sport_type) 
                    VALUES (%s, %s, %s, %s, %s, %s, %s, %s, %s)
                """
                insert_values = (
                    match_id, league_name, home_team, away_team, match_time, match_status,
                    home_score, away_score, 'basketball'
                )
                cursor.execute(insert_query, insert_values)
                
    except Exception as e:
        print(f"获取篮球比赛数据失败: {str(e)}")

# 获取并保存比赛数据
print("开始获取体育赛事数据...")
get_football_matches()
get_basketball_matches()

connection.commit()
cursor.close()
connection.close()

print("数据保存完成！") 